set optimizer_print_missing_stats = off;
-- MPP-23647 Create a partitioned appendonly table, let its age
-- increase during the test.  We will vacuum it at the end of the
-- test.
create table ao_age_test (i int, b bool, c char, d date)
with (appendonly=true)
distributed by (i)
partition by list (b)
(partition b1 values ('f'),
 partition b2 values ('t')
);
insert into ao_age_test select i, (i%123 > 50), (i/11) || '',
'2008/10/12'::date + (i || ' days')::interval
from generate_series(0, 99) i;

create index ao_age_test_i on ao_age_test(i);

-- MPP-23647 Create a empty table with no segments, let its age
-- increase during the test.  We will vacuum it at the end of the
-- test.
create table ao_empty (a int, b int, c varchar)
with (appendonly=true) distributed by (a);

-- MPP-23647 Empty table with non-zero segments but each segment has
-- tupcount = 0.  Also cover column oriented table.
create table aocs_empty (a int, b int, c varchar)
with (appendonly=true, orientation=column) distributed by (a);
insert into aocs_empty select i, i, i::text from generate_series(1,20)i;
-- This update should create a new appendonly segment.
update aocs_empty set c = 'updated' where a > 9;
delete from aocs_empty;
-- Make both the appendonly segments for this table empty.  Let the
-- age of this table grow during the test.  We will vacuum it again at
-- the end.
vacuum aocs_empty;

-- MPP-23647 Ensure pg_class.relhasindex for an empty appendonly table
-- is correctly interpreted by vacuum.
create index ao_empty_a on ao_empty(a);
create index aocs_empty_a on aocs_empty(a);
\d ao_empty
\d aocs_empty

-- vacuum/analyze a table with indexes
create table vactst (i int, b bool, c char, d date);
insert into vactst select i, (i%123 > 50), (i/11) || '', '2008/10/12'::date + (i || ' days')::interval
from generate_series(0, 99) i;

create index vactst_c on vactst (c);
vacuum vactst;
analyze vactst;

create index vactst_b on vactst using bitmap(b);
vacuum vactst;
analyze vactst;

vacuum analyze vactst;

drop table vactst;

create table vactst (i int, b bool, c char, d date);
insert into vactst select i, (i%123 > 50), (i/11) || '', '2008/10/12'::date + (i || ' days')::interval
from generate_series(0, 99) i;

create index vactst_c on vactst (c);
create index vactst_b on vactst using bitmap(b);
vacuum analyze vactst;

drop table vactst;

-- vacuum analyze a table that has dropped a column
create table vactst (i int, b bool, c char, d date);
insert into vactst select i, (i%123 > 50), (i/11) || '', '2008/10/12'::date + (i || ' days')::interval
from generate_series(0, 99) i;
alter table vactst drop column b;
vacuum analyze vactst;

alter table vactst drop column i;
vacuum analyze vactst;
drop table vactst;

-- vacuum analyze a table whose index has pg_statistic stats
create table vactst (i int, b bool, c char, d date);
insert into vactst select i, (i%123 > 50), (i/11) || '', '2008/10/12'::date + (i || ' days')::interval
from generate_series(0, 99) i;
create index vactst_c on vactst (upper(c));
vacuum analyze vactst;
drop table vactst;

-- vacuum analyze an AO table
create table vactst (i int, b bool, c char, d date) with (appendonly=true);
insert into vactst select i, (i%123 > 50), (i/11) || '', '2008/10/12'::date + (i || ' days')::interval
from generate_series(0, 99) i;
vacuum analyze vactst;
drop table vactst;

-- vacuum analyze a partition table
create table vactst (i int, b bool, c char, d date)
distributed by (i)
partition by list (b)
(partition b1 values ('f'),
 partition b2 values ('t')
);
insert into vactst select i, (i%123 > 50), (i/11) || '', '2008/10/12'::date + (i || ' days')::interval
from generate_series(0, 99) i;
vacuum analyze vactst;
drop table vactst;

-- MPP-23647 Vacuum appendonly partitioned table ao_age_test, check
-- that its age is correctly updated along with its partitions.
set vacuum_freeze_min_age=20;
show vacuum_freeze_min_age;
select relname from pg_class where relname like 'ao_age_test%'
order by relname;
vacuum ao_age_test;

-- Note: in checking age() below, be mindful of not checking absolute
-- age value in expected output so as to make the test reliable.

-- Assuming no other activity, vacuum needs one transaction ID for
-- each of the three tables.
-- AO/CO tables should have relfrozenxid = 0.
select relname, relfrozenxid from pg_class
where relname like 'ao_age_test%' and relkind in ('r','p') order by 1;

-- Vacuum the other two empty tables and verify the age of auxiliary tables is
-- updated correctly.
vacuum ao_empty;
vacuum aocs_empty;
-- AO/CO tables should have relfrozenxid = 0.
select relname, relfrozenxid from pg_class
where relname in ('ao_empty', 'aocs_empty') order by 1;
select * from ao_empty;
select * from aocs_empty;

-- Verify that age of appendonly auxiliary tables is update by vacuum.
select 0 < age(relfrozenxid) as age_positive,
       age(relfrozenxid) < 100 as age_within_limit
from pg_class c, pg_appendonly a
where c.oid = a.segrelid and
	   (a.relid = 'ao_empty'::regclass or
	    a.relid = 'aocs_empty'::regclass);
-- Verify that age of toast table is updated by vacuum.
-- AOCS doesn't have a valid reltoastrelid from Greenplum 7.
select 0 < age(relfrozenxid) as age_positive,
       age(relfrozenxid) < 100 as age_within_limit
from pg_class where oid in (select reltoastrelid from pg_class
       where relname = 'ao_empty' or relname = 'aocs_empty');

-- Verify that index is displayed by \d after vacuum.
\d ao_empty;
\d aocs_empty;

-- Ensure that reindex after vacuum works fine.
alter table ao_age_test set with (reorganize='true')
distributed by (c);

-- Force an index scan and verify index lookup work fine after vacuum
-- and reorganize.
set enable_seqscan = false;
select * from ao_age_test where i in (1, 2, 11, 13, 15)
order by i;

drop table ao_age_test;
drop table ao_empty;
drop table aocs_empty;

-- Create some dead tuples, and test VACUUM FULL on the AO/CO auxiliary
-- relations. (There used to be a bug where it didn't do anything, and you got a
-- NOTICE saying a transaction is in progress instead.)
create table co_t1(a int, b int) with (appendonly=true, orientation=column) distributed by(a);
insert into co_t1 select i, i from generate_series(1, 10000) i;
update co_t1 set b = b + 1;
vacuum full co_t1;
drop table co_t1;

create table ao_t1(a int, b int) with (appendonly=true) distributed by(a);
insert into ao_t1 select i, i from generate_series(1, 10000) i;
update ao_t1 set b = b + 1;
vacuum full ao_t1;
drop table ao_t1;

-- superuser must be able to vacuum analyze the table
CREATE ROLE r_priv_test;
CREATE SCHEMA s_priv_test;
CREATE TABLE s_priv_test.t_priv_table(a INT);
INSERT INTO s_priv_test.t_priv_table SELECT i FROM generate_series(1, 10)i;
ALTER TABLE s_priv_test.t_priv_table OWNER TO r_priv_test;
VACUUM ANALYZE s_priv_test.t_priv_table;
DROP SCHEMA s_priv_test CASCADE;
DROP ROLE r_priv_test;

-- Check how reltuples/relpages/relallvisible are updated on a table, on
-- VACUUM ANALYZE.
set gp_autostats_mode='none';
CREATE TABLE vacuum_gp (a int) DISTRIBUTED BY (a);
INSERT INTO vacuum_gp SELECT i FROM generate_series(1, 12)i;
SELECT relname, reltuples, relpages, relallvisible FROM pg_catalog.pg_class WHERE relname like 'vacuum_gp%';
VACUUM ANALYZE vacuum_gp;
SELECT relname, reltuples, relpages, relallvisible FROM pg_catalog.pg_class WHERE relname like 'vacuum_gp%';
reset gp_autostats_mode;

-- Check how reltuples/relpages are updated on a partitioned table, on
-- VACUUM and ANALYZE.
set gp_autostats_mode='none';
CREATE TABLE vacuum_gp_pt (a int, b int) DISTRIBUTED BY (a) PARTITION BY range (b) (END(5), START(5));
INSERT INTO vacuum_gp_pt SELECT 0, 6 FROM generate_series(1, 12);
SELECT relname, reltuples, relpages FROM pg_catalog.pg_class WHERE relname like 'vacuum_gp_pt%';
ANALYZE vacuum_gp_pt;
SELECT relname, reltuples, relpages FROM pg_catalog.pg_class WHERE relname like 'vacuum_gp_pt%';
VACUUM vacuum_gp_pt;
SELECT relname, reltuples, relpages FROM pg_catalog.pg_class WHERE relname like 'vacuum_gp_pt%';
VACUUM ANALYZE vacuum_gp_pt;
SELECT relname, reltuples, relpages FROM pg_catalog.pg_class WHERE relname like 'vacuum_gp_pt%';
reset gp_autostats_mode;

-- Check forbidden relkind for vacuum is correctly skipped
CREATE SEQUENCE s_serial START 100;
VACUUM (ANALYZE, VERBOSE) s_serial;
DROP SEQUENCE s_serial;
VACUUM gp_toolkit.__gp_log_coordinator_ext;

-- Vacuum related access control tests (Issue: https://github.com/greenplum-db/gpdb/issues/9001)
-- Given a non-super-user role
CREATE ROLE non_super_user_vacuum;
-- And a heap table with auxiliary relations under the pg_toast namespace.
CREATE TABLE vac_acl_heap(i int, j text);
-- And an AO table with auxiliary relations under the pg_aoseg namespace.
CREATE TABLE vac_acl_ao(i int, j text) with (appendonly=true);
-- And an AOCS table with auxiliary relations under the pg_aocsseg namespace.
CREATE TABLE vac_acl_aocs(i int, j text) with (appendonly=true, orientation=column);
-- And all the tables belong to the non-super-user role.
ALTER TABLE vac_acl_heap OWNER TO non_super_user_vacuum;
ALTER TABLE vac_acl_ao OWNER TO non_super_user_vacuum;
ALTER TABLE vac_acl_aocs OWNER TO non_super_user_vacuum;
-- We can vacuum each table as the non-super-user
SET ROLE TO non_super_user_vacuum;
VACUUM vac_acl_heap;
VACUUM vac_acl_ao;
VACUUM vac_acl_aocs;
\c
DROP TABLE vac_acl_heap;
DROP TABLE vac_acl_ao;
DROP TABLE vac_acl_aocs;
DROP ROLE non_super_user_vacuum;


-- Vacuum freeze for database with toast attribute in pg_database tuple cause
-- heap_inplace_update raise error "wrong tuple length". This is because system
-- cache flatten toast tuple.
DROP DATABASE IF EXISTS vacuum_freeze_test;
CREATE DATABASE vacuum_freeze_test;
-- start_ignore
create or replace function toast_pg_database_datacl() returns text as $body$
declare
	mycounter int;
begin
	for mycounter in select i from generate_series(1, 2800) i loop
		execute 'create role aaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || mycounter;
		execute 'grant ALL on database vacuum_freeze_test to aaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || mycounter;
	end loop;
	return 'ok';
end;
$body$ language plpgsql volatile strict;

create or replace function clean_roles() returns text as $body$
declare
	mycounter int;
begin
	for mycounter in select i from generate_series(1, 2800) i loop
		execute 'drop role aaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || mycounter;
	end loop;
	return 'ok';
end;
$body$ language plpgsql volatile strict;

select toast_pg_database_datacl();
-- end_ignore
\c vacuum_freeze_test
create temp table before_vacuum as select datname, pg_column_size(datacl) > 8192 as datacl_size, age(datfrozenxid) from pg_database where datname='vacuum_freeze_test';
select datname, datacl_size from before_vacuum;
vacuum freeze;
select datname, pg_column_size(datacl) > 8192 as datacl_size, age(datfrozenxid) != (select age from before_vacuum) as age_changed from pg_database where datname='vacuum_freeze_test';
\c regression
DROP DATABASE vacuum_freeze_test;
-- start_ignore
select clean_roles();
drop function toast_pg_database_datacl();
drop function clean_roles();
-- end_ignore
-- free pg_global space, otherwise it fails db_size_functions
VACUUM FULL pg_authid, pg_database;

-- Test that VACUUM FREEZE should leave no unfrozen rows in pg_stat_last_operation and pg_stat_last_shoperation

drop table if exists vacfrzt;
drop database if exists vacfrzdb;
-- autovacuum needs to be turned off for this test
show autovacuum;

-- helper function to get the number of unfrozen rows in a table
-- as of GPDB7/PG12, we don't have a convenient function to decode the infomask bits like
-- heap_tuple_infomask_flags which only comes after PG13, so just manually checking if it
-- is marked frozen which is (HEAP_XMIN_COMMITTED | HEAP_XMIN_INVALID)=(0x0100 | 0x0200)=768
CREATE OR REPLACE FUNCTION count_unfrozen_rows(rel text)
RETURNS INT AS
$$
DECLARE
    i INTEGER;
    num_pages INTEGER;
    total_count BIGINT := 0;
BEGIN
    SELECT relpages INTO num_pages
    FROM pg_class
    WHERE relname = rel;

    FOR i IN 0..num_pages - 1 LOOP
        total_count := total_count +
        (SELECT count(*)
        FROM heap_page_items(get_raw_page(rel, i))
        WHERE t_infomask & 768 = 0);
    END LOOP;

    RETURN total_count;
END;
$$
LANGUAGE plpgsql;

-- start off cleanly
vacuum freeze;

-- these will create an unfrozen row in pg_stat_last_operation and pg_stat_last_shoperation, respectively
create table vacfrzt(a int);
create database vacfrzdb;

-- we should see unfrozen rows created above.
select count_unfrozen_rows('pg_stat_last_operation');
select count_unfrozen_rows('pg_stat_last_shoperation');

vacuum freeze;

-- now should have zero unfrozen rows
select count_unfrozen_rows('pg_stat_last_operation');
select count_unfrozen_rows('pg_stat_last_shoperation');

-- per-table vacuum freeze of normal table will leave an unfrozen row in pg_stat_last_operation
vacuum freeze vacfrzt;
select count_unfrozen_rows('pg_stat_last_operation');

-- per-table vacuum freeze of the lastop/lastshop won't leave unfrozen rows
vacuum freeze pg_stat_last_operation;
vacuum freeze pg_stat_last_shoperation;

select count_unfrozen_rows('pg_stat_last_operation');
select count_unfrozen_rows('pg_stat_last_shoperation');

-- Multiple VACUUM commands run on a table should not distort the values of reltuples.
CREATE TABLE vac_reltuple_distortion(a int) DISTRIBUTED BY (a);
INSERT INTO vac_reltuple_distortion SELECT generate_series(1, 1000000);
ANALYZE vac_reltuple_distortion;
SELECT reltuples, relname FROM pg_class WHERE oid='vac_reltuple_distortion'::regclass;
VACUUM vac_reltuple_distortion;
VACUUM vac_reltuple_distortion; -- 2nd call to VACUUM after ANALYZE
SELECT reltuples, relname FROM pg_class WHERE oid='vac_reltuple_distortion'::regclass;
VACUUM vac_reltuple_distortion;
SELECT reltuples, relname FROM pg_class WHERE oid='vac_reltuple_distortion'::regclass;
